﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="Category.cs" company="BerlinSoft">
//   Wernfried Schwenkner
// </copyright>
// <summary>
//   Defines the Category type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Windows.Forms;
using SenseTimeTracker.Misc;
using SenseTimeTracker.Properties;
using StedySoft.SenseSDK;
using StedySoft.SenseSDK.DrawingCE;

namespace SenseTimeTracker.BusinessLayer
{
	/// <summary>
	/// This class implements the business layer of categories
	/// </summary>
	public class Category : IColumnSortable
	{
		/// <summary>
		/// Field contains the id column name in the database
		/// </summary>
		public const string IdColumnName = "taskCategoryId";

		/// <summary>
		/// Field contains the name column name in the database
		/// </summary>
		public const string NameColumnName = "taskCategoryName";

		/// <summary>
		/// Field contains the table name in the database
		/// </summary>
		public const string TableName = "TaskCategories";

		/// <summary>
		/// Field contians the category name
		/// </summary>
		private string name;

		/// <summary>
		/// Field contains the category id
		/// </summary>
		private Guid id;

		/// <summary>
		/// Gets the list of all categories
		/// </summary>
		public static List<Category> Categories
		{
			get
			{
				List<Category> taskCategories = new List<Category>();
				SqlCeCommand command = new SqlCeCommand(
						"SELECT " +
						IdColumnName + ", " +
						NameColumnName +
						" FROM " + TableName,
						DatabaseManager.DbConnection);
				bool shallOpen = DatabaseManager.DbConnection.State != System.Data.ConnectionState.Open;
				if (shallOpen)
				{
					DatabaseManager.DbConnection.Open();
				}

				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					taskCategories.Add(new Category
					{
						Id = (Guid)reader[IdColumnName],
						Name = reader[NameColumnName].ToString(),
					});
				}

				if (shallOpen)
				{
					DatabaseManager.DbConnection.Close();
				}

				return taskCategories;
			}
		}

		/// <summary>
		/// Gets the type.
		/// </summary>
		public string Type
		{
			get { return string.Empty; }
		}

		/// <summary>
		/// Gets the number.
		/// </summary>
		public string Number
		{
			get { return string.Empty; }
		}

		/// <summary>
		/// Gets the duration (not used)
		/// </summary>
		public TimeSpan Duration
		{
			get { return TimeSpan.Zero; }
		}

		/// <summary>
		/// Gets the descroption (not used)
		/// </summary>
		public string Description
		{
			get { return string.Empty; }
		}

		/// <summary>
		/// Gets the date (not used)
		/// </summary>
		public DateTime Date
		{
			get { return DateTime.MinValue; }
		}

		/// <summary>
		/// Gets or sets the name of the category
		/// </summary>
		public string Name
		{
			get
			{
				if (id == DatabaseManager.UnknownTaskCategoryId)
				{
					return Resources.UndefinedTaskCategory;
				}

				return name;
			}

			set
			{
				name = value;
			}
		}

		/// <summary>
		/// Gets or sets the id of the category
		/// </summary>
		public Guid Id
		{
			get { return id; }
			set { id = value; }
		}

		/// <summary>
		/// Deletes a category
		/// </summary>
		/// <param name="id">the id of the category to be deleted</param>
		public static void Delete(Guid id)
		{
			SqlCeCommand command = new SqlCeCommand(
				"DELETE FROM " + TableName +
				" WHERE " + IdColumnName + " = @" + IdColumnName,
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			try
			{
				DatabaseManager.DbConnection.Open();
				command.ExecuteNonQuery();
			}
			catch
			{
				if (SenseAPIs.SenseMessageBox.Show(Resources.DialogMessageCategoryRemoveTasksAlso, Resources.DialogHeaderRemoveCategory, SenseMessageBoxButtons.YesNo) == DialogResult.Yes)
				{
					command.CommandText = "DELETE FROM TimeRecords WHERE taskId IN (SELECT taskId FROM Tasks WHERE Tasks.categoryId = @categoryId)";
					command.ExecuteNonQuery();
					command.CommandText = "DELETE FROM Tasks WHERE Tasks.categoryId = @categoryId)";
					command.ExecuteNonQuery();
					command.CommandText = "DELETE FROM Categories WHERE categoryId = @categoryId";
					command.ExecuteNonQuery();
				}
			}
			finally
			{
				DatabaseManager.DbConnection.Close();
			}
		}

		/// <summary>
		/// Inserts a category in the database
		/// </summary>
		public void Insert()
		{
			SqlCeCommand command = new SqlCeCommand(
					"INSERT INTO " + TableName + " (" +
					IdColumnName + ", " +
					NameColumnName + ") " +
					"VALUES (" +
					"@" + IdColumnName + ", " +
					"@" + NameColumnName + ")",
					DatabaseManager.DbConnection);
			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(NameColumnName, name);
			DatabaseManager.DbConnection.Open();
			command.ExecuteScalar();
			DatabaseManager.DbConnection.Close();
		}

		/// <summary>
		/// Updates a category in the database
		/// </summary>
		internal void Update()
		{
			SqlCeCommand command = new SqlCeCommand(
				"UPDATE " + TableName + " SET " +
				NameColumnName + " = @" + NameColumnName +
				" WHERE " + IdColumnName + " = @" + IdColumnName,
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(NameColumnName, name);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}
	}
}
